﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using CustomControlDatabaseInterface.Utilities;

namespace CustomControlDatabaseInterface.Model
{


    /// <summary>
    /// The Purpose of this Class is to maintain Information about all Customers, such as a 
    /// Collection of CustomerNames, the Number of Customers and such. 
    /// It also contains Members to provide filtered Information about all Customers. 
    /// </summary>
    public class FilterableCustomerList : DataBaseListBase<String> {

        private Dictionary<String, List<String>> FilterHistory; 

        public FilterableCustomerList() {
            FillList();
        }

        #region Filtermethods

        public List<String> FilterbyName(String name)
        {
            String pattern = "^" + name;
            List<String> result = List.Where(x => Regex.IsMatch(x, pattern, RegexOptions.IgnoreCase) == true).ToList();
            return result;
        }

        public List<String> FilterByTechnology(String technologyName)
        {
            String query = "USE " + DatabaseUtil.Database + ";";
            String columnName = "kunde";
            query += "SELECT " + columnName + " FROM " + technologyName + " ORDER BY " + columnName + ";";
            List<string> result = DatabaseUtil.GetDataColumn(query, columnName);
            List = RefreshAllCustomers().Intersect(result).ToList();
            return List;
        }

        public List<String> FilterByControl(String technologyName, String  control)
        {
            String query = "USE " + DatabaseUtil.Database + ";";
            String columnName = "kunde";
            query += "Select " + columnName + " FROM " + technologyName + " WHERE ";
            query += "`" + control + "`!='' ";
            query += ";";
            List<String> result = DatabaseUtil.GetDataColumn(query, columnName);
            List = RefreshAllCustomers().Intersect(result).ToList();
            return List;
        }

        #endregion 

        public List<String> GetList() {
            return List; 
        }

        protected override void FillList() {
            List= RefreshAllCustomers();
        }

        List<String> RefreshAllCustomers()
        {
            List<string> tableNames = DatabaseUtil.GetTableNames();
            String columnName = "kunde";
            String query = "USE " + DatabaseUtil.Database + ";";
            if (tableNames.Count < 2)
            {
                query += "SELECT " + columnName + " FROM " + tableNames[0] + " ORDER BY " + columnName + ";";
            }
            else
            {
                query += "SELECT " + columnName + " FROM " + tableNames[0];
                for (int i = 1; i < tableNames.Count; i++)
                {
                    query += " UNION DISTINCT SELECT " + columnName + " FROM " + tableNames[i];
                }
                query += " ORDER BY " + columnName + ";";
            }
            return DatabaseUtil.GetDataColumn(query, columnName);
        }

        public override void UpdateList(List<String> newList) {
            throw new NotImplementedException();
        }
    }
}
